package com.acc.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.acc.entity.Standby;
import com.acc.entity.User;
import com.acc.utils.JDBCTools;
import com.acc.utils.ReflectionUtils;
/**
 * 
 * @author 杨天然
 *
 */
public class HelloDAO {

	/**
	 * 
	 * @param sql
	 * @param objects:预编译参数
	 * @return:取得的用户列表
	 */
	public List<User> getUsers(String sql, Object... objects) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pst = null;
		List<User> list = null;
		ResultSet rs = null;
		try {
			con = JDBCTools.getConnection();
			pst = con.prepareStatement(sql);
			list = new ArrayList<User>();
			int i = 1;
			for (Object object : objects) {

				pst.setObject(i, object);
				i++;
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUserName(rs.getString("user_name"));
				user.setGender(rs.getInt("gender"));
				user.setPassword(rs.getString("password"));
				user.setUserId(rs.getInt("user_id"));
				user.setType(rs.getInt("type"));
				user.setRegisterDate(rs.getDate("registerDate"));
				user.setIdcard(rs.getString("idCard"));
				list.add(user);
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pst, rs);
			;
		}
		return list;
	}

	public User getOne(String sql, Object... objects) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		User user = null;

		try {
			con = JDBCTools.getConnection();
			pst = con.prepareStatement(sql);
			int i = 1;
			for (Object object : objects) {
				pst.setObject(i, object);
				i++;
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
				user.setGender(rs.getInt("gender"));
				user.setUserId(rs.getInt("user_id"));
				user.setType(rs.getInt("type"));
				user.setRegisterDate(rs.getDate("register_date"));
				user.setIdcard(rs.getString("idCard"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pst, rs);
		}
		return user;
	}

	public void addUser(String user_name, String password, Integer gender, String idCard, Integer type) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = "insert into ti_user(user_id,user_name,password,gender,idCard,register_date,type) values(seq_user_id.nextval,?,?,?,?,sysdate,?)";
		try {
			con = JDBCTools.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, user_name);
			pst.setString(2, password);
			pst.setInt(3, gender);
			pst.setString(4, idCard);
			pst.setInt(5, type);
			pst.executeUpdate();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pst, rs);
		}
	}

	public List<String> getAllProvince() {
		// TODO Auto-generated method stub
		String sql = "select * from province";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<String> provinces = new ArrayList<>();
		try {
			con = JDBCTools.getConnection();
			pst = con.prepareStatement(sql);
			int i = 1;
			rs = pst.executeQuery();
			while (rs.next()) {
				provinces.add(rs.getString("province"));
			}
			return provinces;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pst, rs);
		}

		return provinces;
	}

	public List<String> getAllCitiesByProvince(String province) {
		// TODO Auto-generated method stub
		String sql = "select * from city where province = ?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<String> cities = new ArrayList<>();
		try {
			con = JDBCTools.getConnection();
			pst = con.prepareStatement(sql);

			pst.setString(1, province);
			rs = pst.executeQuery();
			while (rs.next()) {
				cities.add(rs.getString("city"));
			}
			return cities;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pst, rs);
		}

		return cities;
	}

}
